Column Operations

The Column Operations nodes are used to edit, manipulate, or calculate values in the table columns. A range of functions allow you to perform basic edits like renaming columns, converting data types, and reordering columns in the table. You can also create calculation columns based on your own PQL expression, generate new date columns by adding or subtracting date parts from an existing date-time column, and more.

Calculated Columns vs Function Presets

The Calculated Column node is a free-form version of the other functions; the remaining functions offer shortcuts to standard column operations, while the calculated column is the more advanced option, producing values based on user-defined PQL expressions.

Configuring Column Operations

The Column Operation nodes can be connected to Select, SQL Query, Bottom N, and Top N nodes. Each required operation node should be connected to the node representing the table containing the columns to be manipulated.

Once connected to the Data Flow, the Column Operations node must be configured from its Properties panel.

Column Operation Nodes

The following Column Operation nodes can be connected to the Data Flow:

  • Calculated Columns: add a new column of values using free-form functions and capabilities based on a given PQL expressions
  • Combine Columns: combine multiple columns into 1.
  • Convert Columns: perform data type conversion on columns.
  • Rename Columns: change column names.
  • Reorder Columns: edit the order of columns in the table.
  • Split Columns: split a column into multiple columns.
  • Add Date Unit: add or subtract a date part from a date-time column.
  • Replace: replace a character, string, or substring with a new one.
  • String Left: include only a substring from the left.
  • String Right: include only a substring from the right.
  • Substring: include only a substring from a string column.
  • Trim: remove a blank space from the start or end of the string.
  • Aggregate FX: perform aggregate functions on selected columns.
  • Math FX: perform mathematical functions on a column.
  • Trig FX: perform trigonometric functions on a column.